﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recSanctionRepo : ISearchable<recSanction>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recSanction", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recSanction", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recSanction", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM ", column);
                            sbQuery.AppendFormat("transactions.recSanction LEFT JOIN master.recDebtorSector ON recSanction.DebtorSectorID = recDebtorSector.DebtorSectorID ");
                            sbQuery.AppendFormat("LEFT JOIN master.recSanctionType ON recSanction.SanctionTypeID = recSanctionType.SanctionTypeID ");
                            sbQuery.AppendFormat("LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID ");
                            sbQuery.AppendFormat("LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID ");
                            sbQuery.AppendFormat("LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID ");
                            sbQuery.AppendFormat("LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID=recDebtor.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recSanction", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recSanction", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public DataTable GetList(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"      recSanction.SanctionID
                                                , recSanction.DebtorSectorID
                                                , recSanction.SanctionTypeID
                                                , recSanction.Value
                                                , recSanction.LetterNo
                                                , recSanction.LetterDate
                                                , recSanction.AttachedFileName
                                                , recSanction.DirectoryLocation
                                                , recSanction.InvoiceNo
                                                , recSanction.InvoiceDate
                                                , recSanction.PaymentNo
                                                , recSanction.PaymentDate
                                                , recSanction.CreateDate
                                                , recSanction.CreateBy
                                                , recSanction.UpdatedDate
                                                , recSanction.UpdatedBy 
                                                , recSanctionType.SanctionTypeName
                                                , recSanction.IsReview 
                                                , recSanction.ReviewBy
                                                , recSanction.ReviewDate 
                                                , recSanction.IsApprove 
                                                , recSanction.ApproveDate
                                                , recSanction.ApproveBy 
                                                , recSubSubSector.SubSubSectorName
                                                , recSubSector.SubSectorName
                                                , recSector.SectorName
                                                , recDebtor.DebtorName
                                                , recSanction.BillingNo
                                                ,recBilling.DueDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recSanction LEFT JOIN master.recDebtorSector ON recSanction.DebtorSectorID = recDebtorSector.DebtorSectorID 
                                                 LEFT JOIN master.recSanctionType ON recSanction.SanctionTypeID = recSanctionType.SanctionTypeID 
                                                 LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                 LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID  
                                                 LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
                                                 LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID=recDebtor.DebtorID 
                                                 LEFT JOIN transactions.recBilling ON recBilling.BillingNo = recSanction.BillingNo");
                    sbQuery.AppendFormat(" {0} ", parameters);

                    cmd.CommandText = sbQuery.ToString();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return dt;
        }

        public List<recSanction> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recSanction> tt = new List<recSanction>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1},recSanction.IsApprove asc,recSanction.CreateDate DESC) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"      recSanction.SanctionID
                                                , recSanction.DebtorSectorID
                                                , recSanction.SanctionTypeID
                                                , recSanction.Value
                                                , recSanction.LetterNo
                                                , recSanction.LetterDate
                                                , recSanction.AttachedFileName
                                                , recSanction.DirectoryLocation
                                                , recSanction.InvoiceNo
                                                , recSanction.InvoiceDate
                                                , recSanction.PaymentNo
                                                , recSanction.PaymentDate
                                                , recSanction.CreateDate
                                                , recSanction.CreateBy
                                                , recSanction.UpdatedDate
                                                , recSanction.UpdatedBy 
                                                , recSanctionType.SanctionTypeName
                                                , recSanction.IsReview 
                                                , recSanction.ReviewBy
                                                , recSanction.ReviewDate 
                                                , recSanction.IsApprove 
                                                , recSanction.ApproveDate
                                                , recSanction.ApproveBy 
                                                , recSubSubSector.SubSubSectorName
                                                , recSubSector.SubSectorName
                                                , recSector.SectorName
                                                , recDebtor.DebtorName
                                                , recSanction.BillingNo
                                                ,recBilling.DueDate");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"      transactions.recSanction LEFT JOIN master.recDebtorSector ON recSanction.DebtorSectorID = recDebtorSector.DebtorSectorID 
                                                 LEFT JOIN master.recSanctionType ON recSanction.SanctionTypeID = recSanctionType.SanctionTypeID 
                                                 LEFT JOIN master.recSubSubSector ON recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                 LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID = recSubSector.SubSectorID  
                                                 LEFT JOIN master.recSector ON recDebtorSector.SectorID = recSector.SectorID
                                                 LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID=recDebtor.DebtorID 
                                                 LEFT JOIN transactions.recBilling ON recBilling.BillingNo = recSanction.BillingNo");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recSanction t = new recSanction(true);
                                t.SanctionID = dr[recSanction.Fields.SanctionID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionID].ToString());
                                t.DebtorSectorID = dr[recSanction.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.DebtorSectorID].ToString());
                                t.SanctionTypeID = dr[recSanction.Fields.SanctionTypeID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionTypeID].ToString());
                                t.Value = dr[recSanction.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recSanction.Fields.Value].ToString());
                                t.LetterNo = dr[recSanction.Fields.LetterNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.LetterNo].ToString();
                                t.LetterDate = dr[recSanction.Fields.LetterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.LetterDate].ToString());
                                t.AttachedFileName = dr[recSanction.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recSanction.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recSanction.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recSanction.Fields.DirectoryLocation].ToString();
                                t.InvoiceNo = dr[recSanction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recSanction.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.InvoiceDate].ToString());
                                t.PaymentNo = dr[recSanction.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recSanction.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.PaymentDate].ToString());
                                t.CreateDate = dr[recSanction.Fields.CreateDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.CreateDate].ToString());
                                t.CreateBy = dr[recSanction.Fields.CreateBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.CreateBy].ToString();
                                t.UpdatedDate = dr[recSanction.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recSanction.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.UpdatedBy].ToString();
                                t.IsReview = dr[recSanction.Fields.IsReview] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsReview].ToString());
                                t.ReviewDate = dr[recSanction.Fields.ReviewDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ReviewDate].ToString());
                                t.ReviewBy = dr[recSanction.Fields.ReviewBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ReviewBy].ToString();
                                t.IsApprove = dr[recSanction.Fields.IsApprove] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsApprove].ToString());
                                t.ApproveDate = dr[recSanction.Fields.ApproveDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ApproveDate].ToString());
                                t.ApproveBy = dr[recSanction.Fields.ApproveBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ApproveBy].ToString();
                                t.BillingNo = dr[recSanction.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.BillingNo].ToString();
                                t.recDebtorSector = new recDebtorSector(true);
                                t.recDebtorSector.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                t.recDebtorSector.recSubSector.SubSectorName = dr[recSubSector.Fields.SubSectorName] == DBNull.Value ? string.Empty : dr[recSubSector.Fields.SubSectorName].ToString();
                                t.recDebtorSector.recSector.SectorName = dr[recSector.Fields.SectorName] == DBNull.Value ? string.Empty : dr[recSector.Fields.SectorName].ToString();
                                t.recDebtorSector.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recSanctionType.SanctionTypeName = dr[recSanctionType.Fields.SanctionTypeName] == DBNull.Value ? string.Empty : dr[recSanctionType.Fields.SanctionTypeName].ToString();
                                t.recBilling.DueDate = dr[recBilling.Fields.DueDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recBilling.Fields.DueDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool UpdateReview(recSanction domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"UPDATE transactions.recSanction 
                                     SET UpdatedDate = @UpdatedDate
                                    , UpdatedBy = @UpdatedBy
                                    , IsReview = @IsReview
                                    , ReviewDate = @ReviewDate
                                    , ReviewBy = @ReviewBy
                                    WHERE SanctionID = @SanctionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@IsReview", SqlDbType.Bit).Value = (object)domain.IsReview ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value = (object)domain.ReviewDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewBy", SqlDbType.VarChar).Value = (object)domain.ReviewBy ?? DBNull.Value;


                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool Open(recSanction domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"UPDATE transactions.recSanction 
                                     SET UpdatedDate = @UpdatedDate
                                    , UpdatedBy = @UpdatedBy
                                    , IsReview = NULL
                                    , ReviewDate = NULL
                                    , ReviewBy = NULL
                                    , IsApprove = NULL
                                    , ApproveDate = NULL
                                    , ApproveBy = NULL
                                    WHERE SanctionID = @SanctionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    //cmd.Parameters.Add("@IsReview", SqlDbType.Bit).Value = (object)domain.IsReview ?? DBNull.Value;
                    //cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value = (object)domain.ReviewDate ?? DBNull.Value;
                    //cmd.Parameters.Add("@ReviewBy", SqlDbType.VarChar).Value = (object)domain.ReviewBy ?? DBNull.Value;
                    //cmd.Parameters.Add("@IsApprove", SqlDbType.Bit).Value = (object)domain.IsApprove ?? DBNull.Value;
                    //cmd.Parameters.Add("@ApproveDate", SqlDbType.DateTime).Value = (object)domain.ApproveDate ?? DBNull.Value;
                    //cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar).Value = (object)domain.ApproveBy ?? DBNull.Value;


                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateApprove(recSanction domain, recBilling domainBilling)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    #region Update Approve Sanction

                    string queryUpdate = @"UPDATE transactions.recSanction 
                                     SET UpdatedDate = @UpdatedDate
                                    , UpdatedBy = @UpdatedBy
                                    , IsApprove = @IsApprove
                                    , ApproveDate = @ApproveDate
                                    , ApproveBy = @ApproveBy
                                    WHERE SanctionID = @SanctionID";

                    SqlCommand cmd = new SqlCommand(queryUpdate, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@IsApprove", SqlDbType.Bit).Value = (object)domain.IsApprove ?? DBNull.Value;
                    cmd.Parameters.Add("@ApproveDate", SqlDbType.Date).Value = (object)domain.ApproveDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar).Value = (object)domain.ApproveBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();


                    #endregion

                    #region Insert ke Billing

                    string queryBilling = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, Balance, PrevBillingNo, BillingDate, DueDate, IsCancel, ForPayment, SaikoNo, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, CalculationID, PaymentValue, IsManual, IsHold, IsPosting, ObjectionNo) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @Balance, @PrevBillingNo, @BillingDate, @DueDate, @IsCancel, @ForPayment, @SaikoNo, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @CalculationID, @PaymentValue, @IsManual, @IsHold, @IsPosting, @ObjectionNo)";
                    SqlCommand cmdBilling = new SqlCommand(queryBilling, conn);

                    cmdBilling.CommandType = CommandType.Text;
                    cmdBilling.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = domainBilling.BillingNo;
                    cmdBilling.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domainBilling.DebtorSectorID;
                    cmdBilling.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = domainBilling.TransTypeID;
                    cmdBilling.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)domainBilling.TariffID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Value", SqlDbType.Decimal).Value = domainBilling.Value;
                    cmdBilling.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)domainBilling.InvoiceNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)domainBilling.InvoiceDetailID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)domainBilling.Balance ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = (object)domainBilling.PrevBillingNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@BillingDate", SqlDbType.Date).Value = (object)domainBilling.BillingDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@DueDate", SqlDbType.Date).Value = (object)domainBilling.DueDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@IsCancel", SqlDbType.Bit).Value = (object)domainBilling.IsCancel ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)domainBilling.ForPayment ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@SaikoNo", SqlDbType.VarChar).Value = (object)domainBilling.SaikoNo ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domainBilling.CreatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domainBilling.CreatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domainBilling.UpdatedDate ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domainBilling.UpdatedBy ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)domainBilling.CalculationID ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = (object)domainBilling.PaymentValue ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@IsManual", SqlDbType.Bit).Value = (object)domainBilling.IsManual ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@IsHold", SqlDbType.Bit).Value = (object)domainBilling.IsHold ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@IsPosting", SqlDbType.Bit).Value = (object)domainBilling.IsPosting ?? DBNull.Value;
                    cmdBilling.Parameters.Add("@ObjectionNo", SqlDbType.VarChar).Value = (object)domainBilling.ObjectionNo ?? DBNull.Value;

                    cmdBilling.Transaction = txn;
                    cmdBilling.ExecuteNonQuery();
                    cmdBilling.Dispose();

                    #endregion

                    #region Update BillingNo ke recSanction

                    string queryUpdateBilling = "UPDATE transactions.recSanction SET  BillingNo = @BillingNo WHERE SanctionID = @SanctionID";
                    SqlCommand cmdUpdateSanction = new SqlCommand(queryUpdateBilling, conn);

                    cmdUpdateSanction.CommandType = CommandType.Text;
                    cmdUpdateSanction.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmdUpdateSanction.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)domainBilling.BillingNo ?? DBNull.Value;

                    cmdUpdateSanction.Transaction = txn;
                    cmdUpdateSanction.ExecuteNonQuery();
                    cmdUpdateSanction.Dispose();

                    #endregion

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool UpdateWithoutFile(recSanction domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recSanction SET DebtorSectorID = @DebtorSectorID, SanctionTypeID = @SanctionTypeID, Value = @Value, LetterNo = @LetterNo, LetterDate = @LetterDate, DirectoryLocation = @DirectoryLocation, InvoiceNo = @InvoiceNo, InvoiceDate = @InvoiceDate, PaymentNo = @PaymentNo, PaymentDate = @PaymentDate, IsReview = @IsReview, ReviewDate = @ReviewDate, ReviewBy = @ReviewBy, IsApprove = @IsApprove, ApproveDate = @ApproveDate, ApproveBy = @ApproveBy, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE SanctionID = @SanctionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    cmd.Parameters.Add("@SanctionTypeID", SqlDbType.Int).Value = domain.SanctionTypeID;
                    cmd.Parameters.Add("@Value", SqlDbType.Money).Value = domain.Value;
                    cmd.Parameters.Add("@LetterNo", SqlDbType.VarChar).Value = (object)domain.LetterNo ?? DBNull.Value;
                    cmd.Parameters.Add("@LetterDate", SqlDbType.DateTime).Value = (object)domain.LetterDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DirectoryLocation", SqlDbType.VarChar).Value = (object)domain.DirectoryLocation ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)domain.InvoiceNo ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = (object)domain.InvoiceDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)domain.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)domain.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@IsReview", SqlDbType.Bit).Value = (object)domain.IsReview ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewDate", SqlDbType.DateTime).Value = (object)domain.ReviewDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ReviewBy", SqlDbType.VarChar).Value = (object)domain.ReviewBy ?? DBNull.Value;
                    cmd.Parameters.Add("@IsApprove", SqlDbType.Bit).Value = (object)domain.IsApprove ?? DBNull.Value;
                    cmd.Parameters.Add("@ApproveDate", SqlDbType.DateTime).Value = (object)domain.ApproveDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ApproveBy", SqlDbType.VarChar).Value = (object)domain.ApproveBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public bool UpdateBillingNo(recSanction domain)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "UPDATE transactions.recSanction SET  BillingNo = @BillingNo WHERE SanctionID = @SanctionID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SanctionID", SqlDbType.Int).Value = domain.SanctionID;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)domain.BillingNo ?? DBNull.Value;

                    try
                    {
                        conn.Open();
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return (result > 0);
        }

        public List<recSanction> GetListNotInObjection(int debtorSectorId)
        {
            List<recSanction> tt = new List<recSanction>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                            select 
	                              [SanctionID]
                                  ,[DebtorSectorID]
                                  ,[SanctionTypeID]
                                  ,[Value]
                                  ,[LetterNo]
                                  ,[LetterDate]
                                  ,[AttachedFileName]
                                  ,[DirectoryLocation]
                                  ,[InvoiceNo]
                                  ,[InvoiceDate]
                                  ,[PaymentNo]
                                  ,[PaymentDate]
                                  ,[IsReview]
                                  ,[ReviewDate]
                                  ,[ReviewBy]
                                  ,[IsApprove]
                                  ,[ApproveDate]
                                  ,[ApproveBy]
                                  ,[IsRec]
                                  ,[CreateDate]
                                  ,[CreateBy]
                                  ,[UpdatedDate]
                                  ,[UpdatedBy]
                                  ,[Attachment]
                                  ,[BillingNo]
                             from transactions.recSanction
                            where (SanctionID not in (select SanctionID from transactions.recObjection)) and (IsApprove = 1) and (DebtorSectorID = @DebtorSectorID) ";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recSanction t = new recSanction();
                                t.SanctionID = dr[recSanction.Fields.SanctionID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionID].ToString());
                                t.DebtorSectorID = dr[recSanction.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.DebtorSectorID].ToString());
                                t.SanctionTypeID = dr[recSanction.Fields.SanctionTypeID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionTypeID].ToString());
                                t.Value = dr[recSanction.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recSanction.Fields.Value].ToString());
                                t.LetterNo = dr[recSanction.Fields.LetterNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.LetterNo].ToString();
                                t.LetterDate = dr[recSanction.Fields.LetterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.LetterDate].ToString());
                                t.AttachedFileName = dr[recSanction.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recSanction.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recSanction.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recSanction.Fields.DirectoryLocation].ToString();
                                t.InvoiceNo = dr[recSanction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recSanction.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.InvoiceDate].ToString());
                                t.PaymentNo = dr[recSanction.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recSanction.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.PaymentDate].ToString());
                                t.IsReview = dr[recSanction.Fields.IsReview] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsReview].ToString());
                                t.ReviewDate = dr[recSanction.Fields.ReviewDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ReviewDate].ToString());
                                t.ReviewBy = dr[recSanction.Fields.ReviewBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ReviewBy].ToString();
                                t.IsApprove = dr[recSanction.Fields.IsApprove] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsApprove].ToString());
                                t.ApproveDate = dr[recSanction.Fields.ApproveDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ApproveDate].ToString());
                                t.ApproveBy = dr[recSanction.Fields.ApproveBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ApproveBy].ToString();
                                t.IsRec = dr[recSanction.Fields.IsRec] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsRec].ToString());
                                t.CreateDate = dr[recSanction.Fields.CreateDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.CreateDate].ToString());
                                t.CreateBy = dr[recSanction.Fields.CreateBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.CreateBy].ToString();
                                t.UpdatedDate = dr[recSanction.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recSanction.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.UpdatedBy].ToString();
                                t.Attachment = dr[recSanction.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recSanction.Fields.Attachment]);
                                t.BillingNo = dr[recSanction.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.BillingNo].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }
        public List<recSanction> GetListNotInObjection(int debtorSectorId, string tglpermohonan)
        {
            List<recSanction> tt = new List<recSanction>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"
                            select 
	                              [SanctionID]
                                  ,transactions.recSanction.DebtorSectorID
                                  ,[SanctionTypeID]
                                  ,transactions.recSanction.Value
                                  ,[LetterNo]
                                  ,[LetterDate]
                                  ,[AttachedFileName]
                                  ,[DirectoryLocation]
                                  ,transactions.recSanction.InvoiceNo
                                  ,[InvoiceDate]
                                  ,[PaymentNo]
                                  ,[PaymentDate]
                                  ,[IsReview]
                                  ,[ReviewDate]
                                  ,[ReviewBy]
                                  ,[IsApprove]
                                  ,[ApproveDate]
                                  ,[ApproveBy]
                                  ,[IsRec]
                                  ,[CreateDate]
                                  ,[CreateBy]
                                  ,transactions.recSanction.UpdatedDate
                                  ,transactions.recSanction.UpdatedBy
                                  ,[Attachment]
                                  ,transactions.recSanction.BillingNo
                             from transactions.recSanction
                             inner join transactions.recBilling on transactions.recBilling.BillingNo=transactions.recSanction.BillingNo
                            where (SanctionID not in (select SanctionID from transactions.recObjection)) and (IsApprove = 1) and (transactions.recSanction.DebtorSectorID = @DebtorSectorID)
                            and transactions.recSanction.InvoiceNo is null and convert(datetime,  CAST(@TglPermohonan AS DATE),112) < transactions.recBilling.DueDate ";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorId;
                    cmd.Parameters.Add("@TglPermohonan", SqlDbType.VarChar).Value = tglpermohonan;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recSanction t = new recSanction();
                                t.SanctionID = dr[recSanction.Fields.SanctionID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionID].ToString());
                                t.DebtorSectorID = dr[recSanction.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.DebtorSectorID].ToString());
                                t.SanctionTypeID = dr[recSanction.Fields.SanctionTypeID] == DBNull.Value ? 0 : int.Parse(dr[recSanction.Fields.SanctionTypeID].ToString());
                                t.Value = dr[recSanction.Fields.Value] == DBNull.Value ? 0 : decimal.Parse(dr[recSanction.Fields.Value].ToString());
                                t.LetterNo = dr[recSanction.Fields.LetterNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.LetterNo].ToString();
                                t.LetterDate = dr[recSanction.Fields.LetterDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.LetterDate].ToString());
                                t.AttachedFileName = dr[recSanction.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recSanction.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recSanction.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recSanction.Fields.DirectoryLocation].ToString();
                                t.InvoiceNo = dr[recSanction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recSanction.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.InvoiceDate].ToString());
                                t.PaymentNo = dr[recSanction.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recSanction.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.PaymentDate].ToString());
                                t.IsReview = dr[recSanction.Fields.IsReview] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsReview].ToString());
                                t.ReviewDate = dr[recSanction.Fields.ReviewDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ReviewDate].ToString());
                                t.ReviewBy = dr[recSanction.Fields.ReviewBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ReviewBy].ToString();
                                t.IsApprove = dr[recSanction.Fields.IsApprove] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsApprove].ToString());
                                t.ApproveDate = dr[recSanction.Fields.ApproveDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.ApproveDate].ToString());
                                t.ApproveBy = dr[recSanction.Fields.ApproveBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.ApproveBy].ToString();
                                t.IsRec = dr[recSanction.Fields.IsRec] == DBNull.Value ? (bool?)null : bool.Parse(dr[recSanction.Fields.IsRec].ToString());
                                t.CreateDate = dr[recSanction.Fields.CreateDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.CreateDate].ToString());
                                t.CreateBy = dr[recSanction.Fields.CreateBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.CreateBy].ToString();
                                t.UpdatedDate = dr[recSanction.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recSanction.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recSanction.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recSanction.Fields.UpdatedBy].ToString();
                                t.Attachment = dr[recSanction.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recSanction.Fields.Attachment]);
                                t.BillingNo = dr[recSanction.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recSanction.Fields.BillingNo].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

    }

}
